{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c47d489c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>当年第几天</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2025-11-05</td>\n",
       "      <td>309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2025-11-12</td>\n",
       "      <td>316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2025-11-19</td>\n",
       "      <td>323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2025-11-26</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期  当年第几天\n",
       "0 2025-11-05    309\n",
       "1 2025-11-12    316\n",
       "2 2025-11-19    323\n",
       "3 2025-11-26    330"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 生成本月内所有周三，并显示 DataFrame\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "\n",
    "def wednesdays_of_month(year=None, month=None):\n",
    "    today = pd.Timestamp.today().normalize()\n",
    "    if year is None:\n",
    "        year = today.year\n",
    "    if month is None:\n",
    "        month = today.month\n",
    "    start = pd.Timestamp(year=year, month=month, day=1)\n",
    "    end = start + pd.offsets.MonthEnd(0)\n",
    "    all_days = pd.date_range(start, end, freq='D')\n",
    "    # pandas 的 weekday: Monday=0, Tuesday=1, Wednesday=2, ...\n",
    "    weds = all_days[all_days.weekday == 2] \n",
    "    df = pd.DataFrame({'日期': weds})\n",
    "    df['当年第几天'] = df['日期'].dt.dayofyear\n",
    "    df = df.reset_index(drop=True)\n",
    "    return df\n",
    "\n",
    "df = wednesdays_of_month()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ad210a2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所有订单的总金额: 237.5\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>type</th>\n",
       "      <th>imgPath</th>\n",
       "      <th>title</th>\n",
       "      <th>price</th>\n",
       "      <th>brand</th>\n",
       "      <th>state</th>\n",
       "      <th>count</th>\n",
       "      <th>_price_clean</th>\n",
       "      <th>_price_num</th>\n",
       "      <th>_count_num</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100</td>\n",
       "      <td>【非自营】</td>\n",
       "      <td>http://p0.jmstatic.com/product/004/879/4879610...</td>\n",
       "      <td>雅芳小黑裙走珠香水女9ml 持久淡香 走珠设计易携带</td>\n",
       "      <td>26.5</td>\n",
       "      <td>雅芳</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "      <td>26.5</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>【非自营】</td>\n",
       "      <td>http://p0.jmstatic.com/product/004/880/4880060...</td>\n",
       "      <td>雅芳 雅芳小黑裙喷雾香水50ml女士经典花香持久淡香优雅</td>\n",
       "      <td>99</td>\n",
       "      <td>雅芳</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "      <td>99.0</td>\n",
       "      <td>1</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102</td>\n",
       "      <td>【非自营】</td>\n",
       "      <td>http://p2.jmstatic.com/product/004/993/4993352...</td>\n",
       "      <td>雅芳走珠香水三支装9ml*3便携易带淡雅清新淡香型滚珠女士淡香水</td>\n",
       "      <td>59</td>\n",
       "      <td>雅芳</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>59</td>\n",
       "      <td>59.0</td>\n",
       "      <td>1</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103</td>\n",
       "      <td>【非自营】</td>\n",
       "      <td>http://p3.jmstatic.com/product/004/880/4880008...</td>\n",
       "      <td>雅芳小红裙走珠香水9ml持久留香便携香水 正品</td>\n",
       "      <td>26.5</td>\n",
       "      <td>雅芳</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "      <td>26.5</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>104</td>\n",
       "      <td>【非自营】</td>\n",
       "      <td>http://p0.jmstatic.com/product/004/880/4880000...</td>\n",
       "      <td>雅芳小金裙走珠香水9ml持久留香便携香水 正品</td>\n",
       "      <td>26.5</td>\n",
       "      <td>雅芳</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "      <td>26.5</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id    type                                            imgPath  \\\n",
       "0  100   【非自营】  http://p0.jmstatic.com/product/004/879/4879610...   \n",
       "1  101   【非自营】  http://p0.jmstatic.com/product/004/880/4880060...   \n",
       "2  102   【非自营】  http://p2.jmstatic.com/product/004/993/4993352...   \n",
       "3  103   【非自营】  http://p3.jmstatic.com/product/004/880/4880008...   \n",
       "4  104   【非自营】  http://p0.jmstatic.com/product/004/880/4880000...   \n",
       "\n",
       "                              title price brand  state  count _price_clean  \\\n",
       "0        雅芳小黑裙走珠香水女9ml 持久淡香 走珠设计易携带  26.5    雅芳  False      1         26.5   \n",
       "1      雅芳 雅芳小黑裙喷雾香水50ml女士经典花香持久淡香优雅    99    雅芳   True      1           99   \n",
       "2  雅芳走珠香水三支装9ml*3便携易带淡雅清新淡香型滚珠女士淡香水    59    雅芳   True      1           59   \n",
       "3           雅芳小红裙走珠香水9ml持久留香便携香水 正品  26.5    雅芳   True      1         26.5   \n",
       "4           雅芳小金裙走珠香水9ml持久留香便携香水 正品  26.5    雅芳   True      1         26.5   \n",
       "\n",
       "   _price_num  _count_num  amount  \n",
       "0        26.5           1    26.5  \n",
       "1        99.0           1    99.0  \n",
       "2        59.0           1    59.0  \n",
       "3        26.5           1    26.5  \n",
       "4        26.5           1    26.5  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 使用 Pandas 读取 carts.json 并计算所有订单的总金额（兼容多种 JSON 结构）\n",
    "import json\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "def load_json(path):\n",
    "    p = Path(path)\n",
    "    if not p.exists():\n",
    "        raise FileNotFoundError(f'文件不存在: {p.resolve()}')\n",
    "    text = p.read_text(encoding='utf-8')\n",
    "    return json.loads(text)\n",
    "\n",
    "def extract_items(obj):\n",
    "    if isinstance(obj, list):\n",
    "        if not obj:\n",
    "            return []\n",
    "        first = obj[0]\n",
    "        if isinstance(first, dict) and 'items' in first:\n",
    "            # 合并所有订单的 items 列表\n",
    "            items = []\n",
    "            for order in obj:\n",
    "                it = order.get('items') or order.get('cart_items') or order.get('goods')\n",
    "                if isinstance(it, list):\n",
    "                    items.extend(it)\n",
    "            return items\n",
    "        # 否则假定这是扁平的商品项列表\n",
    "        return obj\n",
    "    if isinstance(obj, dict):\n",
    "        # 找到第一个 value 是非空 list 且元素为 dict 的项目\n",
    "        for v in obj.values():\n",
    "            if isinstance(v, list) and v:\n",
    "                if isinstance(v[0], dict):\n",
    "                    return v\n",
    "        # 无法识别，返回空\n",
    "        return []\n",
    "    return []\n",
    "\n",
    "def compute_total_amount(df):\n",
    "    # 尝试找到价格列和数量列的候选\n",
    "    cols = [c for c in df.columns]\n",
    "    price_candidates = [c for c in cols if 'price' in c.lower() or '金额' in c or 'amount'==c.lower()]\n",
    "    count_candidates = [c for c in cols if 'count' in c.lower() or 'quantity' in c.lower() or 'num' in c.lower()]\n",
    "    if not price_candidates:\n",
    "        raise ValueError('未在数据中检测到价格列 (price)')\n",
    "    price_col = price_candidates[0]\n",
    "    # 清洗价格字符串，去掉非数字字符（保留小数点和负号）\n",
    "    df['_price_clean'] = df[price_col].astype(str).str.replace(r'[^0-9\\.\\-]', '', regex=True)\n",
    "    df['_price_num'] = pd.to_numeric(df['_price_clean'], errors='coerce')\n",
    "    if count_candidates:\n",
    "        count_col = count_candidates[0]\n",
    "        df['_count_num'] = pd.to_numeric(df[count_col], errors='coerce').fillna(1)\n",
    "    else:\n",
    "        df['_count_num'] = 1\n",
    "    df['amount'] = df['_price_num'] * df['_count_num']\n",
    "    total = df['amount'].sum(min_count=1)\n",
    "    return total, df\n",
    "\n",
    "# 主流程\n",
    "data = load_json('carts.json')\n",
    "items = extract_items(data)\n",
    "if not items:\n",
    "    print('未能从 carts.json 中提取到任何商品项，请检查文件结构。')\n",
    "else:\n",
    "    df_items = pd.json_normalize(items)\n",
    "    try:\n",
    "        total_amount, df_with_amount = compute_total_amount(df_items)\n",
    "        print(f'所有订单的总金额: {total_amount}')\n",
    "        # 在 notebook 中显示前几行以便检查字段\n",
    "        display(df_with_amount.head(20))\n",
    "    except Exception as e:\n",
    "        print('计算总金额时发生错误:', e)\n",
    "        # 仍显示解析后的 DataFrame 帮助调试\n",
    "        display(df_items.head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "092687f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "读取到列: ['type', 'date', 'distance( 万公里)', 'location', 'sell_price( 万元)', 'original_price(原价)']\n",
      "前 20 行:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>date</th>\n",
       "      <th>distance( 万公里)</th>\n",
       "      <th>location</th>\n",
       "      <th>sell_price( 万元)</th>\n",
       "      <th>original_price(原价)</th>\n",
       "      <th>_sell_num</th>\n",
       "      <th>_orig_num</th>\n",
       "      <th>current_vs_original_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>日产 蓝鸟 2018款 1.6L CVT智酷潮音版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.6</td>\n",
       "      <td>大连</td>\n",
       "      <td>10.8</td>\n",
       "      <td>14.59</td>\n",
       "      <td>10.80</td>\n",
       "      <td>14.59</td>\n",
       "      <td>74.023304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>北汽绅宝 绅宝X35 2016款 1.5L 自动豪华版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.8</td>\n",
       "      <td>大连</td>\n",
       "      <td>5.9</td>\n",
       "      <td>9.20</td>\n",
       "      <td>5.90</td>\n",
       "      <td>9.20</td>\n",
       "      <td>64.130435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>吉利 帝豪 2018款 1.5L CVT向上互联版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.6</td>\n",
       "      <td>大连</td>\n",
       "      <td>6.5</td>\n",
       "      <td>9.96</td>\n",
       "      <td>6.50</td>\n",
       "      <td>9.96</td>\n",
       "      <td>65.261044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>川汽野马 野马T70 2016款 升级版 1.5T 手动领先型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>100公里</td>\n",
       "      <td>大连</td>\n",
       "      <td>6.5</td>\n",
       "      <td>8.88</td>\n",
       "      <td>6.50</td>\n",
       "      <td>8.88</td>\n",
       "      <td>73.198198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>启辰T90 2018款 2.0L CVT智联智尚版 国V</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.5</td>\n",
       "      <td>大连</td>\n",
       "      <td>10.05</td>\n",
       "      <td>14.96</td>\n",
       "      <td>10.05</td>\n",
       "      <td>14.96</td>\n",
       "      <td>67.179144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>丰田 威驰FS 2017款 1.5L CVT锋驰版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.8</td>\n",
       "      <td>大连</td>\n",
       "      <td>7.6</td>\n",
       "      <td>9.31</td>\n",
       "      <td>7.60</td>\n",
       "      <td>9.31</td>\n",
       "      <td>81.632653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>雪铁龙C3-XR 2018款 1.6L 自动先锋型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.2</td>\n",
       "      <td>大连</td>\n",
       "      <td>8.31</td>\n",
       "      <td>13.87</td>\n",
       "      <td>8.31</td>\n",
       "      <td>13.87</td>\n",
       "      <td>59.913482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>奔驰GLA级 2018款 GLA 200 动感型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.7</td>\n",
       "      <td>大连</td>\n",
       "      <td>22.8</td>\n",
       "      <td>29.29</td>\n",
       "      <td>22.80</td>\n",
       "      <td>29.29</td>\n",
       "      <td>77.842267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>日产 轩逸 2019款 经典 1.6XE+ CVT智联领先版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.9</td>\n",
       "      <td>大连</td>\n",
       "      <td>8.4</td>\n",
       "      <td>12.62</td>\n",
       "      <td>8.40</td>\n",
       "      <td>12.62</td>\n",
       "      <td>66.561014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>东风风光ix5 2019款 280TGDI CVT智尊型 国V</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.6</td>\n",
       "      <td>大连</td>\n",
       "      <td>9.59</td>\n",
       "      <td>15.17</td>\n",
       "      <td>9.59</td>\n",
       "      <td>15.17</td>\n",
       "      <td>63.216875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>铃木 雨燕 2016款 1.5L 手动时尚型酷玩版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.8</td>\n",
       "      <td>大连</td>\n",
       "      <td>5.98</td>\n",
       "      <td>7.58</td>\n",
       "      <td>5.98</td>\n",
       "      <td>7.58</td>\n",
       "      <td>78.891821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>日产 途达 2018款 2.5L 自动两驱豪华版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.7</td>\n",
       "      <td>大连</td>\n",
       "      <td>17.98</td>\n",
       "      <td>21.69</td>\n",
       "      <td>17.98</td>\n",
       "      <td>21.69</td>\n",
       "      <td>82.895343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>雪佛兰 科沃兹 2019款 320 自动欣悦版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.3</td>\n",
       "      <td>大连</td>\n",
       "      <td>7.09</td>\n",
       "      <td>10.84</td>\n",
       "      <td>7.09</td>\n",
       "      <td>10.84</td>\n",
       "      <td>65.405904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>凯迪拉克XT5 2018款 28T 四驱豪华型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.8</td>\n",
       "      <td>大连</td>\n",
       "      <td>30.98</td>\n",
       "      <td>45.58</td>\n",
       "      <td>30.98</td>\n",
       "      <td>45.58</td>\n",
       "      <td>67.968407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>金杯 海狮X30L 2018款 1.5L商务版DLCG14</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.8</td>\n",
       "      <td>大连</td>\n",
       "      <td>4.5</td>\n",
       "      <td>5.62</td>\n",
       "      <td>4.50</td>\n",
       "      <td>5.62</td>\n",
       "      <td>80.071174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>宝马1系 2018款 改款 118i 时尚型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1</td>\n",
       "      <td>大连</td>\n",
       "      <td>15</td>\n",
       "      <td>21.69</td>\n",
       "      <td>15.00</td>\n",
       "      <td>21.69</td>\n",
       "      <td>69.156293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>本田 飞度 2018款 1.5L CVT舒适天窗版</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.4</td>\n",
       "      <td>大连</td>\n",
       "      <td>7.98</td>\n",
       "      <td>9.31</td>\n",
       "      <td>7.98</td>\n",
       "      <td>9.31</td>\n",
       "      <td>85.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>现代ix25 2017款 1.6L 自动智能型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.5</td>\n",
       "      <td>大连</td>\n",
       "      <td>10.58</td>\n",
       "      <td>14.42</td>\n",
       "      <td>10.58</td>\n",
       "      <td>14.42</td>\n",
       "      <td>73.370319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>长安欧尚科赛 2018款 1.5T 自动 妙 7座</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>0.4</td>\n",
       "      <td>大连</td>\n",
       "      <td>11.11</td>\n",
       "      <td>14.42</td>\n",
       "      <td>11.11</td>\n",
       "      <td>14.42</td>\n",
       "      <td>77.045770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>大众 宝来 2018款 1.5L 自动时尚型</td>\n",
       "      <td>1970-01-01 00:00:00.000002019</td>\n",
       "      <td>1.2</td>\n",
       "      <td>大连</td>\n",
       "      <td>11.3</td>\n",
       "      <td>13.00</td>\n",
       "      <td>11.30</td>\n",
       "      <td>13.00</td>\n",
       "      <td>86.923077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               type                          date  \\\n",
       "0         日产 蓝鸟 2018款 1.6L CVT智酷潮音版 1970-01-01 00:00:00.000002019   \n",
       "1       北汽绅宝 绅宝X35 2016款 1.5L 自动豪华版 1970-01-01 00:00:00.000002019   \n",
       "2         吉利 帝豪 2018款 1.5L CVT向上互联版 1970-01-01 00:00:00.000002019   \n",
       "3   川汽野马 野马T70 2016款 升级版 1.5T 手动领先型 1970-01-01 00:00:00.000002019   \n",
       "4      启辰T90 2018款 2.0L CVT智联智尚版 国V 1970-01-01 00:00:00.000002019   \n",
       "5         丰田 威驰FS 2017款 1.5L CVT锋驰版 1970-01-01 00:00:00.000002019   \n",
       "6         雪铁龙C3-XR 2018款 1.6L 自动先锋型 1970-01-01 00:00:00.000002019   \n",
       "7          奔驰GLA级 2018款 GLA 200 动感型 1970-01-01 00:00:00.000002019   \n",
       "8    日产 轩逸 2019款 经典 1.6XE+ CVT智联领先版 1970-01-01 00:00:00.000002019   \n",
       "9   东风风光ix5 2019款 280TGDI CVT智尊型 国V 1970-01-01 00:00:00.000002019   \n",
       "10        铃木 雨燕 2016款 1.5L 手动时尚型酷玩版 1970-01-01 00:00:00.000002019   \n",
       "11         日产 途达 2018款 2.5L 自动两驱豪华版 1970-01-01 00:00:00.000002019   \n",
       "12          雪佛兰 科沃兹 2019款 320 自动欣悦版 1970-01-01 00:00:00.000002019   \n",
       "13          凯迪拉克XT5 2018款 28T 四驱豪华型 1970-01-01 00:00:00.000002019   \n",
       "14    金杯 海狮X30L 2018款 1.5L商务版DLCG14 1970-01-01 00:00:00.000002019   \n",
       "15           宝马1系 2018款 改款 118i 时尚型 1970-01-01 00:00:00.000002019   \n",
       "16        本田 飞度 2018款 1.5L CVT舒适天窗版 1970-01-01 00:00:00.000002019   \n",
       "17          现代ix25 2017款 1.6L 自动智能型 1970-01-01 00:00:00.000002019   \n",
       "18        长安欧尚科赛 2018款 1.5T 自动 妙 7座 1970-01-01 00:00:00.000002019   \n",
       "19           大众 宝来 2018款 1.5L 自动时尚型 1970-01-01 00:00:00.000002019   \n",
       "\n",
       "   distance( 万公里) location sell_price( 万元)  original_price(原价)  _sell_num  \\\n",
       "0             0.6       大连            10.8               14.59      10.80   \n",
       "1             0.8       大连             5.9                9.20       5.90   \n",
       "2             1.6       大连             6.5                9.96       6.50   \n",
       "3           100公里       大连             6.5                8.88       6.50   \n",
       "4             1.5       大连           10.05               14.96      10.05   \n",
       "5             0.8       大连             7.6                9.31       7.60   \n",
       "6             0.2       大连            8.31               13.87       8.31   \n",
       "7             0.7       大连            22.8               29.29      22.80   \n",
       "8             0.9       大连             8.4               12.62       8.40   \n",
       "9             1.6       大连            9.59               15.17       9.59   \n",
       "10            1.8       大连            5.98                7.58       5.98   \n",
       "11            0.7       大连           17.98               21.69      17.98   \n",
       "12            0.3       大连            7.09               10.84       7.09   \n",
       "13            1.8       大连           30.98               45.58      30.98   \n",
       "14            0.8       大连             4.5                5.62       4.50   \n",
       "15              1       大连              15               21.69      15.00   \n",
       "16            1.4       大连            7.98                9.31       7.98   \n",
       "17            1.5       大连           10.58               14.42      10.58   \n",
       "18            0.4       大连           11.11               14.42      11.11   \n",
       "19            1.2       大连            11.3               13.00      11.30   \n",
       "\n",
       "    _orig_num  current_vs_original_pct  \n",
       "0       14.59                74.023304  \n",
       "1        9.20                64.130435  \n",
       "2        9.96                65.261044  \n",
       "3        8.88                73.198198  \n",
       "4       14.96                67.179144  \n",
       "5        9.31                81.632653  \n",
       "6       13.87                59.913482  \n",
       "7       29.29                77.842267  \n",
       "8       12.62                66.561014  \n",
       "9       15.17                63.216875  \n",
       "10       7.58                78.891821  \n",
       "11      21.69                82.895343  \n",
       "12      10.84                65.405904  \n",
       "13      45.58                67.968407  \n",
       "14       5.62                80.071174  \n",
       "15      21.69                69.156293  \n",
       "16       9.31                85.714286  \n",
       "17      14.42                73.370319  \n",
       "18      14.42                77.045770  \n",
       "19      13.00                86.923077  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "统计: 当前售价列（数值）缺失数: 0  原价（数值）缺失数: 3\n"
     ]
    },
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m在当前单元格或上一个单元格中执行代码时 Kernel 崩溃。\n",
      "\u001b[1;31m请查看单元格中的代码，以确定故障的可能原因。\n",
      "\u001b[1;31m单击<a href='https://aka.ms/vscodeJupyterKernelCrash'>此处</a>了解详细信息。\n",
      "\u001b[1;31m有关更多详细信息，请查看 Jupyter <a href='command:jupyter.viewOutput'>log</a>。"
     ]
    }
   ],
   "source": [
    "# 读取 guazi.csv 并生成 DataFrame，按日期降序排列，添加当前售价与原价的百分比列\n",
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "\n",
    "def read_csv_fallback(path='guazi.csv'):\n",
    "    p = Path(path)\n",
    "    if not p.exists():\n",
    "        raise FileNotFoundError(f'文件不存在: {p.resolve()}')\n",
    "    try:\n",
    "        return pd.read_csv(p, encoding='utf-8')\n",
    "    except Exception:\n",
    "        # 尝试常见的中文编码\n",
    "        # pandas.read_csv does not support 'errors' argument, so we ignore it\n",
    "        return pd.read_csv(p, encoding='gbk')\n",
    "\n",
    "df = read_csv_fallback('guazi.csv')\n",
    "print('读取到列:', list(df.columns))\n",
    "\n",
    "# 识别并解析日期列（尝试常见名称）\n",
    "date_col = next((c for c in df.columns if 'date' in c.lower() or '日期' in c or '时间' in c), None)\n",
    "if date_col is not None:\n",
    "    df[date_col] = pd.to_datetime(df[date_col], errors='coerce')\n",
    "    df = df.sort_values(by=date_col, ascending=False).reset_index(drop=True)\n",
    "else:\n",
    "    print('未识别到日期列，数据未排序。')\n",
    "\n",
    "# 寻找现价与原价列（自动匹配常见关键字）\n",
    "def find_col(cols, keywords):\n",
    "    for c in cols:\n",
    "        low = c.lower()\n",
    "        for kw in keywords:\n",
    "            if kw in low:\n",
    "                return c\n",
    "    return None\n",
    "\n",
    "sell_keywords = ['sell', 'sell_price', 'sellprice', 'sell price', 'sell_price (万元)', 'current', 'sell_price（万元）']\n",
    "orig_keywords = ['original', 'original_price', 'originalprice', '原价']\n",
    "\n",
    "sell_col = find_col(df.columns, sell_keywords)\n",
    "orig_col = find_col(df.columns, orig_keywords)\n",
    "\n",
    "if sell_col is None:\n",
    "    raise ValueError('未找到当前售价列（sell/...），请检查列名: ' + ','.join(df.columns))\n",
    "if orig_col is None:\n",
    "    print('未找到原价列（original/...），将仅显示当前售价')\n",
    "\n",
    "def to_numeric_series(s):\n",
    "    return pd.to_numeric(s.astype(str).str.replace(r'[^0-9\\.\\-]', '', regex=True), errors='coerce')\n",
    "\n",
    "df['_sell_num'] = to_numeric_series(df[sell_col])\n",
    "df['_orig_num'] = to_numeric_series(df[orig_col]) if orig_col is not None else pd.Series([None]*len(df))\n",
    "\n",
    "# 计算百分比（当前售价 / 原价 * 100），如果原价缺失或为0则结果为NaN\n",
    "df['current_vs_original_pct'] = (df['_sell_num'] / df['_orig_num']) * 100\n",
    "\n",
    "print('前 20 行:')\n",
    "display(df.head(20))\n",
    "print('统计: 当前售价列（数值）缺失数:', df['_sell_num'].isna().sum(), ' 原价（数值）缺失数:', df['_orig_num'].isna().sum())"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py39",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.23"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
